/*
 * Copyright 2019-2021 the langrundata.
 */
package com.caishi.lkx.common.util;


import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.*;
import java.util.*;


/**
 * @author by ChenKai
 * date: 2021/5/24 14:17
 */
public class ExcelUtil {

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @return 包含文件数据的工作簿对象
     */
    private static Workbook getWorkbook(InputStream inputStream) throws IOException, InvalidFormatException {
        return WorkbookFactory.create(inputStream);
    }

    /**
     * 将单元格内容转换为字符串
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        return cell.toString();
//        String returnValue = null;
//        switch (cell.getCellType()) {
//            //数字
//            case NUMERIC:
//                Double doubleValue = cell.getNumericCellValue();
//                // 格式化科学计数法，取一位整数，如取小数，值如0.0,取小数点后几位就写几个0
//                DecimalFormat df = new DecimalFormat("0.00");
//                returnValue = df.format(doubleValue);
//                break;
//            //字符串
//            case STRING:
//                returnValue = cell.getStringCellValue();
//                break;
//            //布尔
//            case BOOLEAN:
//                boolean booleanValue = cell.getBooleanCellValue();
//                returnValue = Boolean.toString(booleanValue);
//                break;
//            // 空值
//            case BLANK:
//                break;
//            // 公式
//            case FORMULA:
//                returnValue = cell.getCellFormula();
//                break;
//            // 故障
//            case ERROR:
//                break;
//            default:
//                break;
//        }
//        return returnValue;
    }

    /**
     * 处理Excel内容转为List<Map<String,Object>>输出
     * workbook：已连接的工作薄
     * startRow：读取的开始行数（默认填0，0开始,传过来是EXcel的行数值默认从1开始，这里已处理减1）
     * endRow：读取的结束行数（填-1为全部）
     * ExistTop:是否存在头部（如存在则读取数据时会把头部拼接到对应数据，若无则为当前列数）
     */
    private static List<List<Map<String, Object>>> handleData(Workbook workbook, int startRow, int endRow, boolean existTop, String sheetNum) {
        //声明返回结果集result
        List<List<Map<String, Object>>> result;
        //解析sheet（sheet是Excel脚页）
        if ("-1".equals(sheetNum)) {
            Integer[] sheetNumArray = new Integer[workbook.getNumberOfSheets()];
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheetNumArray[i] = i;
            }
            result = readData(workbook, startRow, endRow, existTop, sheetNumArray);
        } else {
            String[] split = sheetNum.split(",");
            Integer[] sheetNumArray = new Integer[split.length];
            for (int i = 0; i < split.length; i++) {
                sheetNumArray[i] = Integer.valueOf(split[i]);
            }
            result = readData(workbook, startRow, endRow, existTop, sheetNumArray);
        }
        return result;
    }

    /**
     * HandleData函数数据读取实际操作的抽离部分
     */
    private static List<List<Map<String, Object>>> readData(Workbook workbook, int startRow, int endRow, boolean existTop, Integer[] sheetNumArray) {
        //声明返回结果集result
        List<List<Map<String, Object>>> result = new ArrayList<>();
        //声明一个Excel头部函数
        ArrayList<String> top = new ArrayList<>();
        for (Integer integer : sheetNumArray) {
            //声明中间返回结果集result
            List<Map<String, Object>> middleResult = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(integer);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            //如存在头部，处理头部数据
            if (existTop) {
                int firstRowNum = sheet.getFirstRowNum();
                Row firstRow = sheet.getRow(firstRowNum);
                if (null == firstRow) {
                    System.out.println("解析Excel失败，在第一行没有读取到任何数据！");
                }
                for (int i = 0; i < Objects.requireNonNull(firstRow).getLastCellNum(); i++) {
                    top.add(convertCellValueToString(firstRow.getCell(i)));
                }
            }
            //处理Excel数据内容
            int endRowNum;
            //获取结束行数
            if (endRow == -1) {
                endRowNum = sheet.getPhysicalNumberOfRows();
            } else {
                endRowNum = Math.min(endRow, sheet.getPhysicalNumberOfRows());
            }
            //遍历行数
            for (int i = startRow - 1; i < endRowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                Map<String, Object> map = new HashMap<>(7);
                //获取所有列数据
                for (int y = 0; y < row.getLastCellNum(); y++) {
                    if (top.size() > 0) {
                        if (top.size() >= y) {
                            map.put(top.get(y), convertCellValueToString(row.getCell(y)));
                        } else {
                            map.put(String.valueOf(y + 1), convertCellValueToString(row.getCell(y)));
                        }
                    } else {
                        map.put(String.valueOf(y + 1), convertCellValueToString(row.getCell(y)));
                    }
                }
                middleResult.add(map);
            }
            result.add(middleResult);
        }
        return result;
    }

    /**
     * 方法一
     * 根据行数和列数读取Excel
     * fileName:Excel文件路径
     * startRow：读取的开始行数（默认填0）
     * EndRow：读取的结束行数（填-1为全部）
     * ExistTop:是否存在头部（如存在则读取数据时会把头部拼接到对应数据，若无则为当前列数）
     * SheetNum:Excel工作表页数，以,分割，全选为-1(从0开始的整数，就是Excel下面那个sheet)
     * 返回一个List<List<Map<String,Object>>>
     */
    public static List<List<Map<String, Object>>> readExcelByRC(File file, int startRow, int endRow, boolean existTop, String sheetNum) throws Exception {

        //声明返回的结果集
        List<List<Map<String, Object>>> result = new ArrayList<>();
        //声明一个工作薄
        Workbook workbook = null;
        //声明一个文件输入流
        FileInputStream inputStream = null;
        try {
            // 获取Excel工作簿
            inputStream = new FileInputStream(file);
            workbook = getWorkbook(inputStream);
            //处理Excel内容
            result = handleData(workbook, startRow, endRow, existTop, sheetNum);
        } catch (Exception e) {
            System.out.println("解析Excel失败 错误信息：" + e.getMessage());
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                System.out.println(("关闭数据流出错！错误信息：" + e.getMessage()));
            }
        }
        return result;
    }

    public static File writer(String fileName, String fileType, List<Map<String, Object>> list, String[] titleRow) throws IOException {
        Workbook wb;
        String excelPath = File.separator + fileName + "." + fileType;
        File file = new File(excelPath);
        Sheet sheet = null;
        //创建工作文档对象
        if (!file.exists()) {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
            //创建sheet对象
            sheet = wb.createSheet("sheet1");
            OutputStream outputStream = new FileOutputStream(excelPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();

        } else {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
        }
        //创建sheet对象
        if (sheet == null) {
            sheet = wb.createSheet("sheet1");
        }

        //添加表头
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeight((short) 540);
        //创建第一行
        cell.setCellValue(fileName);
        // 样式对象
        CellStyle style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);

        // 样式，居中
        cell.setCellStyle(style);
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short) 280);
        style.setFont(font);
        // 单元格合并
        // 四个参数分别是：起始行，起始列，结束行，结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleRow.length - 1));
        sheet.autoSizeColumn(5200);

        //创建第二行
        row = sheet.createRow(1);
        for (int i = 0; i < titleRow.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(titleRow[i]);
            // 样式，居中
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 20 * 256);
        }
        row.setHeight((short) 540);

        //循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 2);
            row.setHeight((short) 500);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < titleRow.length; j++) {
                row.createCell(j).setCellValue((String) map.get(j + 1 + ""));
            }

        }

        //创建文件流
        OutputStream stream = new FileOutputStream(excelPath);
        //写入数据
        wb.write(stream);
        //关闭文件流
        stream.close();
        return file;
    }

    public static File writer2(String fileName, String fileType, List<Map<String, Object>> list, String[] titleRow, String firstRowName) throws IOException {
        Workbook wb;
        String excelPath = File.separator + fileName + "." + fileType;
        File file = new File(excelPath);
        Sheet sheet = null;
        //创建工作文档对象
        if (!file.exists()) {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
            //创建sheet对象
            sheet = wb.createSheet("sheet1");
            OutputStream outputStream = new FileOutputStream(excelPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();

        } else {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
        }
        //创建sheet对象
        if (sheet == null) {
            sheet = wb.createSheet("sheet1");
        }

        //添加表头
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeight((short) 1080);
        //创建第一行
        cell.setCellValue(firstRowName);
        // 样式对象
        CellStyle style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setWrapText(true);

        // 样式，居中
        cell.setCellStyle(style);
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short) 280);
        style.setFont(font);
        // 单元格合并
        // 四个参数分别是：起始行，起始列，结束行，结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleRow.length - 1));
        sheet.autoSizeColumn(5200);

        //创建第二行
        row = sheet.createRow(1);
        for (int i = 0; i < titleRow.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(titleRow[i]);
            // 样式，居中
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 20 * 256);
        }
        row.setHeight((short) 540);

        //循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 2);
            row.setHeight((short) 500);
            for (int j = 0; j < titleRow.length; j++) {
                Object o = list.get(i).get(j + "");
                row.createCell(j).setCellValue(null == o ? null : o.toString());
            }

        }

        //创建文件流
        OutputStream stream = new FileOutputStream(excelPath);
        //写入数据
        wb.write(stream);
        //关闭文件流
        stream.close();
        return file;
    }

    public static File writerForExport(String fileName, String fileType, List<Map<String, Object>> list, String[] titleRow, Boolean isJoin) throws IOException {
        Workbook wb;
        String excelPath = File.separator + fileName + "." + fileType;
        File file = new File(excelPath);
        Sheet sheet = null;
        //创建工作文档对象
        if (!file.exists()) {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
            //创建sheet对象
            sheet = wb.createSheet("sheet1");
            OutputStream outputStream = new FileOutputStream(excelPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } else {
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }
        }
        //创建sheet对象
        if (sheet == null) {
            sheet = wb.createSheet("sheet1");
        }
        //添加表头
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeight((short) 540);
        //创建第一行
        cell.setCellValue(fileName);
        // 样式对象
        CellStyle style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);

        // 样式，居中
        cell.setCellStyle(style);
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short) 280);
        style.setFont(font);
        // 单元格合并
        // 四个参数分别是：起始行，起始列，结束行，结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
        sheet.autoSizeColumn(5200);

        //创建第二行
        row = sheet.createRow(1);
        for (int i = 0; i < titleRow.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(titleRow[i]);
            // 样式，居中
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 20 * 256);
        }
        row.setHeight((short) 540);

        //循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 2);
            row.setHeight((short) 500);
            row.createCell(0).setCellValue((String) (list.get(i)).get("studentCode"));
            row.createCell(1).setCellValue((String) (list.get(i)).get("userName"));
            row.createCell(2).setCellValue((String) (list.get(i)).get("classesName"));
            row.createCell(3).setCellValue((String) (list.get(i)).get("schoolName"));
            row.createCell(4).setCellValue((String) (list.get(i)).get("collegeName"));
            row.createCell(5).setCellValue((String) (list.get(i)).get("departmentName"));
            if (isJoin) {
                row.createCell(6).setCellValue((String) (list.get(i)).get("phoneNumber"));
            } else {
                row.createCell(6).setCellValue("pass".equals((list.get(i)).get("status")) ? "已入班" : "未入班");
            }
        }
        //创建文件流
        OutputStream stream = new FileOutputStream(excelPath);
        //写入数据
        wb.write(stream);
        //关闭文件流
        stream.close();
        return file;
    }

    public static MultipartFile fileToMultipartFile(File file) {
        FileItem fileItem = createFileItem(file);
        return new CommonsMultipartFile(fileItem);
    }

    private static FileItem createFileItem(File file) {
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem item = factory.createItem("textField", "text/plain", true, file.getName());
        int bytesRead;
        byte[] buffer = new byte[8192];
        try {
            FileInputStream fis = new FileInputStream(file);
            OutputStream os = item.getOutputStream();
            while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return item;
    }
}
